#!/usr/bin/env python2.5

import psycopg2
import psycopg2.extensions
import StringIO
import logging

print >> sys.stderr, """
888888888888888888888888888888888888888888888888888888888888888888888

    table_????
        

        Description:
                Obtain 
        Populates DB tables:


888888888888888888888888888888888888888888888888888888888888888888888
"""


# add self to search path for testing
if __name__ == '__main__':
    exe_path, exe_name = os.path.split(os.path.abspath(sys.argv[0]))
    sys.path.append(os.path.abspath(os.path.join(exe_path,"..", "python_modules")))
    myname = os.path.splitext(exe_name)[0];
else:
    myname = __name__

import panda, postgres
import general_util, ortho_species
from custom_log import custom_log
from general_util import die_error

# log
status_log = custom_log(myname)
status_log.info('\n')


# common path for all parameter files
import get_parameter_path
parameter_path = get_parameter_path.parameter_path






#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#       Functions


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

#_________________________________________________________________________________________

#   retrieve_data

#_________________________________________________________________________________________
from collections import default_dict
def retrieve_exon_coordinates(cursor, species, chromosome):
    #"
    cursor.execute("""
        SELECT 
                strand, 
                prot_id, 
                exon_id, 
                exon_beg, 
                exon_end 
            FROM 
                taxon.gene_chromosome_strand NATURAL JOIN 
                ens_id NATURAL JOIN 
                taxon.prot_exons
            WHERE
                species = '%s' AND
                chromosome = '%s'
    """ % (species, chromosome))  #"
    prot_exons = cursor.fetchall()

    # 
    #   save per peptide
    # 
    prot_id_to_exons = default_dict(list)
    prot_id_to_strand = dict() 
    for strand, prot_id, exon_id, start, finish in prot_exons:
        prot_id_to_strand[prot_id] =strand
        prot_id_to_exons[prot_id].append((exon_id, start, finish))

    # sort by exons start
    for prot_id in prot_id_to_exons.keys():
        prot_id_to_exons[prot_id].sort(key=operator.itemgetter(1), 
                                     reverse=not prot_id_to_strand[prot_id])
        
    return prot_id_to_exons, prot_id_to_strand



#_________________________________________________________________________________________

#       save_table_prot_coding_introns

#       helper function

#_________________________________________________________________________________________
def save_table_prot_coding_introns(cursor, prot_id_chrm_to_exon_coding_data):
    """
        Save to table prot_coding_introns
            all data from one species
    """
    prot_id_to_introns = default_dict(list)
    logger.debug("  Save table prot_coding_exons")
    for chrm in prot_id_chrm_to_exon_coding_data.keys():
        for prot_id, exons in prot_id_chrm_to_exon_coding_data[chrm].iteritems():

            introns = parse_introns(exons, exons[0].strand)
            # save
            prot_id_to_introns[prot_id] = introns
            for rank, (beg, end, coding) in enumerate(introns):
                fields = [prot_id, beg, end, end - beg, rank, coding]
                sql_strs.append("\t".join (map(str, fields)))
    sql_str = "\n".join(sql_strs) + "\n"
    column_names =  [ "prot_id",
                      "intron_beg",
                      "intron_end",
                      "length",
                      "rank",
                      "coding"]        
    table_name = "taxon.prot_coding_introns(" + ",".join(column_names) + ")"
    cursor.copy_from(StringIO.StringIO(sql_str), table_name)

    return prot_id_to_introns


#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# Main logic

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


table_names = [ "prot_coding_exons"     ,
                "prot_coding_introns"   ,
                "prot_exon_stats"       ,
                "prot_coding_structures"]


#
#   connect to panda
# 
dbh = panda.connect_to_panda()

truncate_and_vacuum_full_tables(dbh, ["taxon.%s" % n for n in table_names] )

cursor = dbh.cursor()


#
#   get chromosomes 
#
species_chromosomes = get_species_and_chromosomes (cursor)



#
#       Get DB parameters
#
taxon_data = panda.get_taxon_data();
for taxon in taxon_data.keys():
    #
    #   ignore virtual taxa
    # 
    if taxon_data[taxon]["virtual"]:
        continue

    #
    # get data
    
    #
    # save data
    


dbh.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
for table_name in table_name:
    cursor.execute("VACUUM ANALYSE taxon.%s" % table_name);



dbh.close()






        







